package cn.hxzy.dao;

import cn.hxzy.entity.News;
import cn.hxzy.util.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDao extends BaseDao<News> {

    public News findByIdWithUser(int id) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT n.*,u.`name` user_name FROM`news` n LEFT JOIN `user` u ON n.`user_id`=u.`id` WHERE n.`id`=?");
        statement.setObject(1, id);
        ResultSet resultSet = statement.executeQuery();
        News news = null;
        if (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getTimestamp("creat_time"));
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return news;
    }

    public List<News> findAllWithUser() throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.`name` user_name from news n left join `user` u on n.`user_id`=u.`id`");
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getDate("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }


    /**
     * 查询 user名字信息
     */
    public List<News> findByUserNameLike(Integer page, Integer size, String users) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.name user_name from news n left join `user` u on n.`user_id`=u.`id` where u.name = ? limit ?,? ");
        statement.setObject(1, "%" + users + "%");
        statement.setObject(2, (page - 1) * size);
        statement.setObject(3, size);
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getTimestamp("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    /**
     * 查询名字相关数据
     */
    public List<News> findByNameLike(Integer page, Integer size, String name) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.name user_name from news n left join `user` u on n.`user_id`=u.`id` where n.name like ? limit ?,? ");
        statement.setObject(1, "%" + name + "%");
        statement.setObject(2, (page - 1) * size);
        statement.setObject(3, size);
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getTimestamp("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    /**
     * 查询id相关数据
     */
    public List<News> findByIdLike(Integer page, Integer size, int id) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.name user_name from news n left join `user` u on n.`user_id`=u.`id` where n.id = ? limit ?,? ");
        statement.setObject(1, id);
        statement.setObject(2, (page - 1) * size);
        statement.setObject(3, size);
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getTimestamp("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    /**
     * 查询全部信息
     */
    public List<News> findAllWithUserAll(Integer page, Integer size) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.`name` user_name from news n left join `user` u on n.`user_id`=u.`id`limit ?,?");
        statement.setObject(1, (page - 1) * size);
        statement.setObject(2, size);
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getDate("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    /**
     * 查询个数——>关于名字的内容
     */
    public long countAllWithUser(String name) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM news n LEFT JOIN `user` u ON n.`user_id`=u.`id` WHERE n.name LIKE ?");
        statement.setObject(1, "%" + name + "%");
        ResultSet resultSet = statement.executeQuery();
        long count = 0;
        if (resultSet.next()) {
            count = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        System.out.println("*****************\n" + count + "\n*****************");
        return count;
    }

    /**
     * 查询个数——>关于id的内容
     */
    public long countAllWithId(int id) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM news n LEFT JOIN `user` u ON n.`user_id`=u.`id` WHERE n.id= ?");
        statement.setObject(1, id);
        ResultSet resultSet = statement.executeQuery();
        long count = 0;
        if (resultSet.next()) {
            count = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        System.out.println("*****************\n" + count + "\n*****************");
        return count;
    }

    /**
     * 查询个数——>关于时间的内容
     */
    public long countAllWithDate(String date) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM news n LEFT JOIN `user` u ON n.`user_id`=u.`id` WHERE n.creat_time= ?");
        statement.setObject(1, date);
        ResultSet resultSet = statement.executeQuery();
        long count = 0;
        if (resultSet.next()) {
            count = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        System.out.println("*****************\n" + count + "\n*****************");
        return count;
    }
    /**
     * 查询全部信息
     */
    public List<News> findByDateLike(Integer page, Integer size, String date) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.name user_name from news n left join `user` u on n.`user_id`=u.`id` where n.creat_time = ? limit ?,? ");
        statement.setObject(1, date);
        statement.setObject(2, (page - 1) * size);
        statement.setObject(3, size);
        ResultSet resultSet = statement.executeQuery();
        List<News> list = new ArrayList<>();
        while (resultSet.next()) {
            News news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getTimestamp("creat_time"));
            list.add(news);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    /**
     * 查询个数——>关于user名字的内容
     */
    public long countAllWithUserName(String users) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM news n LEFT JOIN `user` u ON n.`user_id`=u.`id` WHERE u.name= ?");
        statement.setObject(1, users);
        ResultSet resultSet = statement.executeQuery();
        long count = 0;
        if (resultSet.next()) {
            count = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        System.out.println("*****************\n" + count + "\n*****************");
        return count;
    }

    /**
     * 添加新闻关于user的名字
     * @return
     */
    public News addByUser(News news) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("select n.*,u.`name` user_name from news n left join `user` u on n.`user_id`=u.`id`");
        ResultSet resultSet = statement.executeQuery();
        News news1=null;
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getString("new_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            news.setId(resultSet.getInt("id"));
            news.setCreatTime(resultSet.getDate("creat_time"));
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return news;
    }
}
